﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Gacn.ControleAlunos.Library.Entities;

namespace Gacn.ControleAlunos.Library.DataAccess
{
    internal static class TurmaCalendarioDataAccess
    {
        #region Get Methods
        public static DataTable GetAllTurmaCalendarioEntity()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;

            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "SELECT tbTurmaCalendario.DataAula, tbTurmaCalendario.idTurmaCalendario, tbTurma.idEscola, tbCurso.idCurso FROM tbTurmaCalendario INNER JOIN tbTurma ON tbTurmaCalendario.idTurma = tbTurma.idTurma INNER JOIN tbCurso ON tbTurma.idCurso = tbCurso.idCurso";
            command.CommandType = CommandType.Text;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            return dataTable;
        }
        #endregion

        #region Insert Methods
        /// <summary>
        /// Método não testado
        /// </summary>
        /// <param name="turmaCal"></param>
        /// <returns></returns>
        public static Boolean NewTurmaCalendarioEntity(TurmaCalendarioEntity turmaCal)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction;

            connection = Util.GetConnection();
            connection.Open();
            transaction = connection.BeginTransaction("Cria Calendário");

            command.Connection = connection;
            command.Transaction = transaction;

            try
            {
                foreach (DateTime data in turmaCal.Datas)
                {

                    if (data.Equals(turmaCal.Datas[0]))
                    {
                        command.CommandText = "INSERT INTO [dbo].[tempCargaCalendario] ([idCurso],[idEscola],[DataAula],[iDtempCargaCalendario]) VALUES(?, ?, ?)";
                        command.CommandType = CommandType.Text;

                        command.Parameters.Add("idCurso", SqlDbType.Int).Value = turmaCal.CursoID;
                        command.Parameters.Add("idCurso", SqlDbType.Int).Value = turmaCal.EscolaID;
                        command.Parameters.Add("DataAula", SqlDbType.DateTime).Value = data;
                    }
                    else
                    {
                        command.Parameters.Add("DataAula", SqlDbType.DateTime).Value = data;
                    }

                    command.ExecuteNonQuery();
                }

                // Realiza o commit da transação
                transaction.Commit();
                return true;

            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                Console.WriteLine("  Message: {0}", ex.Message);

                // Attempt to roll back the transaction.
                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred
                    // on the server that would cause the rollback to fail, such as
                    // a closed connection.
                    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                    Console.WriteLine("  Message: {0}", ex2.Message);
                }

                return false;
            }
        }

        public static Boolean NewTurmaCalendarioEntity(List<TurmaCalendarioEntity> turmasCal)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction;

            connection = Util.GetConnection();
            connection.Open();
            transaction = connection.BeginTransaction("Cria Calendário");

            command.Connection = connection;
            command.Transaction = transaction;

            try
            {
                foreach (TurmaCalendarioEntity turmaCal in turmasCal)
                {
                    foreach (DateTime data in turmaCal.Datas)
                    {

                        if (data.Equals(turmaCal.Datas[0]))
                        {
                            command.CommandText = "INSERT INTO [dbo].[tempCargaCalendario] ([idCurso],[idEscola],[DataAula],[iDtempCargaCalendario]) VALUES(?, ?, ?)";
                            command.CommandType = CommandType.Text;

                            command.Parameters.Add("idCurso", SqlDbType.Int).Value = turmaCal.CursoID;
                            command.Parameters.Add("idCurso", SqlDbType.Int).Value = turmaCal.EscolaID;
                            command.Parameters.Add("DataAula", SqlDbType.DateTime).Value = data;
                        }
                        else
                        {
                            command.Parameters.Add("DataAula", SqlDbType.DateTime).Value = data;
                        }

                        command.ExecuteNonQuery();
                    }
                }

                // Realiza o commit da transação
                transaction.Commit();
                return true;

            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                Console.WriteLine("  Message: {0}", ex.Message);

                // Attempt to roll back the transaction.
                try
                {
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred
                    // on the server that would cause the rollback to fail, such as
                    // a closed connection.
                    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                    Console.WriteLine("  Message: {0}", ex2.Message);
                }

                return false;
            }
        }

        #endregion

        #region Control Methods


        /// <summary>
        /// Método realiza a inserção definitiva no banco de dados das TurmaCalendarioEntity
        /// A proc que é executada registra o calendário de cada turma baseando-se no Curso 
        /// Ex: Curso: DOM-A, Turmas: HOMERO_2011_A_12, HOMERO_2011_A_13
        ///    Essa proc irá pegar a data que está relacionada com o curso e inserir nas respectivas turmas
        /// </summary>
        public static Boolean FinalizaNewTurmaCalendarioEntity()
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            DataTable dataTable = null;


            connection = Util.GetConnection();
            connection.Open();

            command = connection.CreateCommand();
            command.CommandText = "@@@@@@@@@@FinalizaNewTurmaCalendarioEntity@@@@@@@@@";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("SUCESSO", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("ProcComment", SqlDbType.VarChar).Direction = ParameterDirection.Output;

            dataTable = new DataTable();
            dataTable.Locale = CultureInfo.InvariantCulture;
            new SqlDataAdapter(command).Fill(dataTable);

            //Não estou utilizando o dataTable de retorno

            if (!(bool)command.Parameters["SUCESSO"].Value)
            {
                throw new Exception(string.Concat("Ocorreu um erro ao executar a procedure. \r\n", command.Parameters["ProcComment"].Value.ToString()));
            }

            return Boolean.Parse(command.Parameters["SUCESSO"].Value.ToString());
        }


        #endregion

    }
}
